const mysqlSrv = require('./mysqlServer')


module.exports = {

  // 插入用户信息
  insertStuInfo: async function (stuInfo) {

    // 插入语句
    const sql = 'INSERT INTO stuinfo(uid,sno,sname,department,grade,sex,logintime,curzc) VALUE(?,?,?,?,?,?,?,?)'

    // 获取插入字段
    const { uid, userId, userName, userDepartment, grade, userSex, loginTime, DQZC } = stuInfo

    // 执行插入语句

    return await mysqlSrv.query(sql, [uid, userId, userName, userDepartment, grade, userSex, loginTime, DQZC])


  },

  insertScroeInfo: async function (scoreInfo) {
    const sql = 'INSERT INTO scoreinfo(sno,courseid,coursename,coursenature,credit,score,jd,xn,xq) VALUE(?,?,?,?,?,?,?,?,?)'
    return await mysqlSrv.query(sql, Object.values(scoreInfo))
  },
  insertSchedule: async function (courseInfo) {

    const sql = 'INSERT INTO courseinfo(sno,courseid,coursename,addr,`day`,`from`,`to`,`start`,`end`,xn,xq,bg) VALUE(?,?,?,?,?,?,?,?,?,?,?,?)'

    mysqlSrv.query(sql, Object.values(courseInfo)).catch(err => {
      console.log(err)
    })
  },
  insertPowerate: async function (powerRateData) {
    const sql = 'INSERT INTO otherinfo(sno,powerrate) VALUE(?,?);'
    mysqlSrv.query(sql, Object.values(powerRateData))
  },
  insertEcard: async function (ecardData) {
    const sql = 'INSERT INTO otherinfo(sno,ecard) VALUE(?,?);'
    mysqlSrv.query(sql, Object.values(ecardData))
  },
  queryStuInfoBySno: async function (sno) {
    const sql = 'SELECT * FROM stuinfo WHERE sno=?'
    return await mysqlSrv.query(sql, sno)
  },
  queryStuInfoByUid: async function (uid) {
    const sql = 'SELECT * FROM stuinfo WHERE uid=?'
    let result = {}
    result = await mysqlSrv.query(sql, uid)
    return result
  },
  queryUidBySno: async function (sno) {
    const sql = 'SELECT uid FROM stuinfo WHERE sno=?'
    let result = await mysqlSrv.query(sql, sno)
    return result
  },
  queryScore: async function (uid, xn, xq) {
    const sql = 'SELECT scoreinfo.courseid,scoreinfo.coursename,scoreinfo.coursenature,scoreinfo.score,scoreinfo.jd,scoreinfo.credit FROM scoreinfo LEFT JOIN stuinfo ON scoreinfo.sno=stuinfo.sno WHERE uid=? AND xn=? AND xq=?'
    let result = await mysqlSrv.query(sql, [uid, xn, xq]).catch(err => {
      console.log('查询出错:', err)
    })
    return result
  },
  queryLoginTime: async function (uid) {
    const sql = 'SELECT logintime FROM stuinfo WHERE uid=?'
    let result = await mysqlSrv.query(sql, uid)
    return result

  },
  querySchedule: async function (uid, xn, xq, zc) {

    const sql = 'SELECT courseinfo.sno,courseinfo.courseid,courseinfo.coursename,courseinfo.addr,courseinfo.day,courseinfo.from,courseinfo.to,courseinfo.start,courseinfo.end,courseinfo.xn,courseinfo.xq,courseinfo.bg FROM courseinfo LEFT JOIN stuinfo ON courseinfo.sno=stuinfo.sno WHERE uid=? AND xn=? AND xq=? AND start<=? AND end>=?'
    let result = await mysqlSrv.query(sql, [uid, xn, xq, zc, zc])
    return result
  },
  queryPowerRate: async function (uid) {
    const sql = 'SELECT otherinfo.sno,powerrate FROM otherinfo LEFT JOIN stuinfo ON stuinfo.sno=otherinfo.sno WHERE uid=?'
    let result = await mysqlSrv.query(sql, uid)
    return result
  },
  queryEcard: async function (uid) {
    const sql = 'SELECT otherinfo.sno,ecard FROM otherinfo LEFT JOIN stuinfo ON stuinfo.sno = otherinfo.sno WHERE uid=?;'

    let result = await mysqlSrv.query(sql, uid)
    return result
  },
  updateLoginTime: async function (uid, loginTime) {
    const sql = 'UPDATE stuinfo SET logintime=? WHERE uid=?'
    let result = await mysqlSrv.query(sql, [loginTime, uid])
    return result
  },
  updateScore: function (scoreData) {
    let { score, sno, courseid } = scoreData
    const sql = 'UPDATE scoreinfo SET score=? WHERE sno=? AND courseid=?;'
    mysqlSrv.query(sql, [score, sno, courseid])
  },
  updateSchedule: function () {

  },
  updatePowerRate: function (powerRateData) {
    let { sno, powerrate } = powerRateData
    const sql = 'UPDATE otherinfo SET powerrate=? WHERE sno=?;'
    mysqlSrv.query(sql, [powerrate, sno])
  },
  updateEcard: function (ecardData) {
    let { sno, ecard } = ecardData

    const sql = 'UPDATE otherinfo SET ecard=? WHERE sno=?;'
    mysqlSrv.query(sql, [ecard, sno])
  }
}